package com.apes.fa.respository;

import com.apes.fa.model.FaCard;
import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @BelongsProject: apes-server
 * @BelongsPackage: com.apes.fa.repository
 * @ClassName: FaCardRepository
 * @Author: PengZiFeng
 * @Description: 资产建卡服务
 * @CreateTime: 2021-08-23 10:46
 * @Version: 1.0
 */
public interface FaCardRepository extends CustomRepository<FaCard,String> {

/*    @Query(value = "" +
            " SELECT " +
            " fa.*" +
            " FROM " +
            " fa_card fa " +
            " WHERE " +
            " fa.Fa_CATEGORY_ID in :faCategoryId" +
            " AND fa.COST_CENTER_ID = :costCenterId" +
            " AND fa.DEPRECIATION_MARK = 1" + //盘点是可以显示提取折旧和非提取折旧的，过滤掉那些已报废，盘亏的才对
            " AND fa.STATE = 'approve' "
            ,nativeQuery = true)
    List<FaCard> findCardByCostCenterAndCategory(@Param("costCenterId")String costCenterId, @Param("faCategoryId")List<String> faCategoryId);*/

    @Query(value = "" +
            " SELECT " +
            " fa.*" +
            " FROM " +
            " fa_card fa " +
            " WHERE " +
            " fa.Fa_CATEGORY_ID in :faCategoryId" +
            " AND fa.COST_CENTER_ID = :costCenterId" +
            " AND fa.STATE = 'approve' "
            ,nativeQuery = true)
    List<FaCard> findCardByCostCenterAndCategory(@Param("costCenterId")String costCenterId, @Param("faCategoryId")List<String> faCategoryId);

    @Query(value = "" +
            " SELECT " +
            " fa.*" +
            " FROM " +
            " fa_card fa " +
            " WHERE " +
            " fa.COMPANY_ID = :companyId" +
            " AND fa.FA_TYPE = :faType" +
            " AND fa.ENABLE_DATE < to_date(:date,'yyyy-MM-dd')" +
            " AND fa.DEPRECIATION_MARK = :falg" +
            " AND fa.state = 'approve' "
            ,nativeQuery = true)
    List<FaCard> findCardByCompanyIdAndFaType(@Param("companyId")String companyId, @Param("faType")String faType,@Param("date") String firstDate,@Param("falg") int falg);


    @Query(value = "" +
            " SELECT " +
            " fa.*" +
            " FROM " +
            " fa_card fa " +
            " WHERE " +
            " fa.ID = :id" +
            " AND fa.STATE = 'approve'" +
            " AND fa.DEPT_ID = :deptId " +
//            " AND fa.DEPRECIATION_MARK = 1" +
            " AND FA.FA_CATEGORY_ID LIKE 'W01%' "
            ,nativeQuery = true)
    FaCard findByIdAndBusinessState(@Param("id")String id,@Param("deptId")String deptId);

    @Query(value = "" +
            " SELECT " +
            " fa.*" +
            " FROM " +
            " fa_card fa " +
            " WHERE " +
            " fa.ID = :id" +
            " AND fa.STATE = 'approve'" +
            " AND fa.DEPT_ID = :deptId " +
            " AND (FA.FA_CATEGORY_ID LIKE 'W02%' OR FA.FA_CATEGORY_ID LIKE 'W03%')"
            ,nativeQuery = true)
    FaCard findByIdAndBusinessStateDZ(@Param("id")String id,@Param("deptId")String deptId);

    @Query(value = "" +
            " SELECT " +
            " fa.*" +
            " FROM " +
            " fa_card fa " +
            " WHERE " +
            " fa.ID = :id" +
            " AND fa.STATE = 'approve'" +
            " AND fa.dept_id = :deptId "
            ,nativeQuery = true)
    FaCard findChangeById(@Param("id")String id,@Param("deptId")String deptId);

    @Query(value = " SELECT A.* FROM fa_card A " +
            "WHERE A.COMPANY_ID = :companyId " +
            "AND A.FA_TYPE = :faType " +
            "AND A.ENABLE_DATE < to_date(:date,'yyyy-MM-dd') " +
            "AND A.DEPRECIATION_MARK = :flag " +
            "AND A.REMAINING_DEPRECIATION_MONTH > 0 " +
            "AND A.STATE = 'approve'",nativeQuery = true)
    List<FaCard> findFaCardByCarryOverPL(@Param("companyId")String companyId, @Param("faType")String faType,@Param("date") String date,@Param("flag") int flag);


    @Query(value = " SELECT A.* FROM fa_card A,fa_card_account B " +
            "WHERE A.COMPANY_ID = :companyId " +
            "AND A.FA_TYPE = :faType " +
            "AND A.ENABLE_DATE < to_date(:date,'yyyy-MM-dd') " +
            "AND A.DEPRECIATION_MARK = :flag " +
            "AND A.REMAINING_DEPRECIATION_MONTH > 0 " +
            "AND A.STATE = 'approve' " +
            "AND B.FA_CARD_ID = A.ID " +
            "AND TO_CHAR(B.BOOKKEEPING_DATE,'YY-MM') = TO_CHAR(to_date(:date,'yyyy-MM-dd'),'YY-MM')",nativeQuery = true)
    List<FaCard> findByCarryOverPL(@Param("companyId")String companyId, @Param("faType")String faType,@Param("date") String date,@Param("flag") int flag);
}
